Suzy B Software 2

home *** CD-ROM | disk | FTP | other *** search

/ Suzy B Software 2 / Suzy B Software CD-ROM 2 (1994).iso / finances / sheet30d / dbman.txt < prev next >

Wrap

Text File | 1995-04-25 | 41.3 KB | 1,057 lines

SHEET version 3.0 Manual of Database Part By Chor-ming Lung Date: November 1, 1989 Table of contents _________________ Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Database creation . . . . . . . . . . . . . . . . . . . . . . . . 1 Criteria range . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Comparison criteria . . . . . . . . . . . . . . . . . . 2 Match criteria . . . . . . . . . . . . . . . . . . . . . 2 Computed criteria . . . . . . . . . . . . . . . . . . . 2 Combining criteria . . . . . . . . . . . . . . . . . . . . . 2 Database operations . . . . . . . . . . . . . . . . . . . . . . . 3 Set Database . . . . . . . . . . . . . . . . . . . . . . . . 3 Set Criteria range . . . . . . . . . . . . . . . . . . . . . 3 Find . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Extract . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Set Output range . . . . . . . . . . . . . . . . . . . . . . 5 Delete . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Database functions . . . . . . . . . . . . . . . . . . . . . . . . 6 DSUM, DCOUNT, DAVERAGE, DMIN, DMAX, DSTD, DVAR . . . . . . . 6 Database commands . . . . . . . . . . . . . . . . . . . . . . . . 7 QUERY . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 EXTRACT . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 DB_INPUT DB_INPUT . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Input record . . . . . . . . . . . . . . . . . . . . . . 8 Query . . . . . . . . . . . . . . . . . . . . . . . 8 Search . . . . . . . . . . . . . . . . . . . . . . 8 Next . . . . . . . . . . . . . . . . . . . . . . . 8 Query forms . . . . . . . . . . . . . . . . . . . . . . 10 Find records . . . . . . . . . . . . . . . . . . . . . . 10 DB_INPUT arguments . . . . . . . . . . . . . . . . . . . 10 Database . . . . . . . . . . . . . . . . . . . . . 10 Display . . . . . . . . . . . . . . . . . . . . . . 10 Query . . . . . . . . . . . . . . . . . . . . . . . 11 Relation list . . . . . . . . . . . . . . . . . . . 11 DB_INPUT entry . . . . . . . . . . . . . . . . . . . . . 11 BUTTON . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 CUR_REC . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Phone Book II . . . . . . . . . . . . . . . . . . . . . . . . . . 13 SHEET -- Database Part By Chor-ming Lung November 1, 1989 Database management ___________________ Introduction: ____________ SHEET v3.0 provides database functions and operations which are quite similar to Lotus 123. If you are familiar with the database manager of Lotus 123, feel free to jump to the part starting from the discussion of DB_INPUT. DB_INPUT We will use an address book and a small personnel database as examples to guide you through different aspects of the database manager of SHEET. First of all, we will discuss the creation of database. After that, we will discuss about searching criteria and combining criteria to form criteria range for manipulating information from the database (creating queries and using database functions/opertions). BASIC database commands are introduced. Finally, we will try to write an application to handle our address book. Database creation: _________________ Before you enter any record, we need to decide what elements constitute the database we need. If we are creating address book, we need to store the name of the person, address and his phone number. Those elements (name, address, phone number) are called fields of a fields database. You can refine the field NAME to First Name, Middle Name, ____ Last Name and ADDRESS to Street, City, State and Zip code. In our _______ address book, we use one field to store names of persons we are interested. The ADDRESS is splited as mentioned above. Sure, it takes up more columns to store the address but it is easier to find particular records. So, we have the following fields in our address book: Name ____ Street ______ City ____ State _____ Zip code ________ The next step in creating database is to find a place to put our database. To simplify everything, we start the database from cell A1. The first row of a spreadsheet database stores the field names. So, we enter Name in A1, Street in B1 .... We have some data stored in ____ ______ FONBKII.SHT for our address book database. Our second example is in DATABASE.SHT. It is a simplified personnel database. It consists of the following fields: Last Name _________ Date Hired __________ Age ___ Sex ___ Salary ______ D 1 SHEET -- Database Part By Chor-ming Lung November 1, 1989 Criteria range: ______________ Once you have created the database, you can locate, extract or delete data from the database. To find the records desired, we need to set up criteria to confine the search to some special group of data. Spreadsheet database manager uses spatial structure to represent AND and OR relationships between criteria. So, we need more than a cell to define our criteria. Those cells which hold our criteria is called criteria range. A criteria range is a special form of database. The first row of a criteria range is reserved for field names. The rest rows define a set of criteria. Criteria on each row will be AND together and criteria on different rows will be OR together. Criteria: ________ Comparison criteria: Comparison criteria Only numeric values can be compared. Comparison operators >, <, <>, >=, =>, <=, =< can all be used. Here is an example of our two cells criteria: Age ___ >50 It means from the database, find all persons who are over 50 years old. Match criteria: Match criteria Text string can be searched by partial match. For example: Name ____ an It will match names like "Anita" or "Alan". The match is case-insensitive. Computed criteria: Computed criteria The field name of computed criteria should be blank. The formula on the computed criteria consists of cell reference of the first record. For example, the first record on field Age is on cell C2, the following ___ criteria find persons who are greater than 30 and less than 50. ___ +c2>30 and c2<50 Combining criteria: __________________ If you are looking for records which satisfy the following condition on our personnel database: age>50 and sex is male OR age>40 and salary<30000 the criteria range looks like this: Age ___ Sex ___ Salary ______ >50 m D 2 SHEET -- Database Part By Chor-ming Lung November 1, 1989 >40 <30000 NOTE: NOTE If a criteria range contains a blank row, then the whole database will meet the criteria. Database operations: ___________________ Before we go further, we play with the file DATABASE.SHT to get acquaintance with the database operations first and understand what they are later. - load the file DATABASE.SHT. - move the mouse cursor over cell A1 - press <Control> key and then press the left mouse button - move the mouse cursor over cell E11 - press <Shift> key and then press the left mouse button Mouse click with <Control> and <Shift> define a block containing cells from A1 to E11. The block will be highlited by inverting to black. - move the mouse cursor over the menu title Data and select the Data menu item Set Database. Set Database The highlited block is gone. It means the database is designated to cells A1..E11. If you want to see the designated area again, select the menu item Set Database. Set Database - select the menu item Set Criteria range. Set Criteria range The block A14..E15 will be displayed. Well, the database, criteria range and output range are pre-selected and saved with DATABASE.SHT. The database is A1..E11, Criteria range is A14..E15 and the Output range is G1..J11. The criteria is: Age ___ <30 It means we want to find persons whose ages are under 30. Well, select the Find menu item. The record cursor is on the person name Holt. If Find you press the Down arrow key, the record cursor will move to the girl whose name is Peacock. If you press the Down arrow key again, the record cursor stays where it is. It means no more records meet the criteria. NOTE: the topmost line changes from Ready to Find. You must NOTE Ready Find click on any cell to change from Find mode to Ready. Find Ready Let us try to extract records to our output range. Select the Extract menu item. You should see that the data of Holt and Peacock Extract are copied to our output range. The information of Date Hire is not in our output range. That is because the first row confines the fields being extracted. To experience more, you can modify the criteria, expand the criteria range and play with it again. But you should not save your D 3 SHEET -- Database Part By Chor-ming Lung November 1, 1989 modified version of DATABASE.SHT. We will use it later. D 4 SHEET -- Database Part By Chor-ming Lung November 1, 1989 Now, it is time to take a look of the menu items under the menu title Data. Data Set Database: Set Database Designate the current block to be the database being manipulated. NOTE a database includes the field names and records. If you select a block before choosing this menu item, the definition of the block will be inserted into Database range edit field. Database range Otherwise, you have to enter it by yourself. Set Criteria range: Set Criteria range Designate the current block to be the criteria range. A criteria range contains field names and criteria. Set Output range: Set Output range Output range is used to designate an area for data extraction. The first row of the output range specifies which fields being extract. See Extract for more detail. Extract Find: Find Use the criteria defined on the criteria range to show records that match the criteria. If there are some records that match the criteria, then the status will be changed from Ready to Find. If you press the Ready Find arrow key Up/Down, the record cursor will move to the next record that meets the criteria. To change back from Find mode to Find Ready mode, you should mouse-click on any Ready cell. Extract: Extract Extracts records which meet the criteria. The records will be stored in the area defined by Output range. NOTE: Except the first row, NOTE Extract will erase cells within the output range. If your output range has one row only, cells beneath this row and within the columns of output range will be erased. If you have 5 records that meet the criteria, but your output range is only 4 rows big, only three records will be stored in the output range (the first row contains field names). Delete: Delete Deletes records that meet the criteria. If deletion happens, your database range will be shorten. Delete affects only cells within Delete database range. D 5 SHEET -- Database Part By Chor-ming Lung November 1, 1989 Database functions: __________________ There are seven database functions built-in. They are: DSUM, DCOUNT, DAVERAGE, DMIN, DMAX, DSTD, DVAR DSUM, DCOUNT, DAVERAGE, DMIN, DMAX, DSTD, DVAR Here is the syntax of our database functions: Dfun(database, index, criteria) database: database it is the block which holds the data index: index it can be an offset number or field name. The first field has offset of 1. Salary on our DATABASE.SHT is the fifth field. Its offset number is 5. Instead of using 5, we can use the field name "Salary" as the index. criteria: criteria it is the block which holds the criteria range. In our DATABASE.SHT, cell E13 has a formula: + DAVERAGE(A1..E11,"Salary",A14..E15) It means we want to find the average of Salary from the records which meet criteria A14..E15. D 6 SHEET -- Database Part By Chor-ming Lung November 1, 1989 Database commands: _________________ There are four commands and one function in the BASIC interpreter which relates with database manipulation. They are QUERY, EXTRACT, DB_INPUT, BUTTON and function CUR_REC QUERY, EXTRACT, DB_INPUT, BUTTON CUR_REC QUERY QUERY Syntax: QUERY database, criteria, cell database is the block which holds the data. criteria is the block which holds the criteria range. cell is the leading cell which we put the record numbers which meet the criteria. cell will hold the number of cell records which meet the criteria. The cells below cell cell will hold the actual record number. In our DATABASE.SHT file, if you run cell F12, you will see that cell k1 is 2. It means we have 2 records meet the criteria. K2 is 1 and K3 is 3. The actual records are record 1 (Holt) and record 3 (Peacock). EXTRACT EXTRACT Syntax: EXTRACT database, criteria_range, output_range It works similar to the Extract operation on the menu. Extract DB_INPUT DB_INPUT Syntax: DB_INPUT database, display, query, "relation list" It is possibly the most complex command for SHEET. For the time being, we will go to load the DATABASE.SHT file and have some hands-on experience first. - load the DATABASE.SHT file - Press <Control> and P simultaneously - Press <Return> to print our spreadsheet first - Press <Shift> and left arrow key simultaneously - move the mouse cursor over L11 - press <Alternate> and press the left mouse button to run the single command program. A dialog box similar to a window shows up. If you click on the close box, the dialog box will be closed and it ends the command DB_INPUT. Let us take a closer look of the dialog box. Besides of the close box, the title of the dialog box is Input record. There are Input record 9 boxes under the title. The first three of them are Query Search Query Search and Next. The rest 6 boxes are empty. Below the boxes, we see the Next first record of our personnel database. There is a vertical D 7 SHEET -- Database Part By Chor-ming Lung November 1, 1989 slider which we can use to move from record to record. The last line shows how many records in our database and what the current record number is. Input record: Input record ____________ If you move your mouse cursor to the box Query and press the Query left button, you will see that the title is Query form now, and Query form the contents of the first three boxes change to Main and Find. If Main Find you click on the Find box, the title changes to Find record and Find Find record the boxes change to Main and Query. If you click on the Main box, Main Query Main you will get back to Input record. If you click on the Query box, Input record Query you will get back to Query form. Query form Let us go back to the Input record mode. If the title on Input record your dialog box is not showing Input record, you can press Input record function key F1 a few times to switch back to Input record. Input record Pressing function key F1 is equivalent to clicking on the first box. F2 is equivalent to second box... Now we are in Input mode. Press the Left arrow key four Input times to move the field cursor to the field Sex, and click on the button Search. Enter the character m and press return. The record Search m of Jones will be displayed. If you press F3 (Next button), Lamar Jones Next Lamar will be shown. Keep pressing F3, you will reach Allen and a Allen message "String not found" follows. Let us go to the first record by moving the slider bar. Move the mouse cursor over the date and press the left button. The field cursor moves from Sex to Date Hired. If you press <Esc>, Sex Date Hired the date content is gone. Now enter 3-4-82 and press left arrow 3-4-82 key. The field cursor moves to Age. We change the age to 22 and Age press return. The field cursor moves to Salary automatically. Salary Remeber the editing direction? The field cursor moves to the editing direction direction of the last arrow key led to. To sum up: we have three different modes: Input, Query and _________________________________________________ Find and the 9 boxes are actually mouse buttons. Function keys _________________________________________________________________ from F1 to F9 simulate the nine mouse buttons. ______________________________________________ In input mode: _____________ Query button: Query activate the query form for fancy query. Search button: Search search for simple text Next button: Next search using the same text from Search Search In query mode: _____________ Main button: Main return to Input mode Input Find button: Find show records that match the criteria defined in the query forms. In find mode: ____________ Main button: Main return to Input mode Input Query button: Query return to Query mode Query The data input form accepts date in this format: mm-dd-yy (more on this later). The line editor works the same way as usual. We can move from field to field by pressing the arrow keys or mouse D 8 SHEET -- Database Part By Chor-ming Lung November 1, 1989 click on the content of a field. We can move record to record by moving the slider bar or by pressing <Shift and Up/Down arrow>. D 9 SHEET -- Database Part By Chor-ming Lung November 1, 1989 Query forms: Query forms ___________ If we press F1, we should be in Query mode. Let us look at Query the query forms we currently have. The first form means that we want to find those records of which the Age field is over 50 and Age the Sex field is m. If we press <Shift-down arrow>, we have the Sex second form. It means Sex is f and Salary is over 40000. The Sex Salary combined effect is: find all the records of which (Age>50 and Sex is m) or (Sex is f and Salary>40000) Age Sex Sex Salary Find records: Find records ____________ Let us press F2 to go to the Find mode. The record shown is Find Francis. The rest are Farley and Allen. They match the criteria Francis Farley Allen in our query forms. For fun, you can change the query forms and see the result. DB_INPUT arguments: DB_INPUT arguments __________________ DB_INPUT database, display, query, "relation list" In our example (cell G16), DB_INPUT A1..E11,T1..W7,L2..P10,"1,2,3,5,4" Database: _________ A1..E11 is the area which hold our personnel database. NOTE: NOTE DB_INPUT determines the maximum number of record by the number of continuous non-empty rows on the database. If the database is A1..E1000, it will still say that the maximum number is 10, because the row A12..E12 is empty. If you use a larger row number on your database, you can add more records to your database without having to modify the argument from time to time. Display: _______ The following shows cells T1..W7. T U V W ________________________________________ 1 2 Last Name: Last Name 3 4 Date Hired: Age: Date Hired Age 5 6 Salary: Sex: Salary Sex 7 DB_INPUT scans the display area from left to right and top to bottom. If it finds a cell is non-empty, it will match the cell to the field defines in the relation list. In relation list _____________ the above example, Last Name maps to field 1, Date Hired to Last Name Date Hired field 2, Age to field 3, Salary to field 5 and Sex to field Age Salary Sex 4. The column widths of U and W confine the line editor U W D 10 SHEET -- Database Part By Chor-ming Lung November 1, 1989 widths. Query: _____ L2..P10 is our criteria range. Here is the list of our criteria range: L M N O P 2 L:Name Date HireAge Sex Salary 3 >50 m 4 f >40000 NOTE: NOTE The query range is L2..P10, DB_INPUT eliminates empty rows from the query range to create the actual criteria range for query. Relation list: _____________ The relation list helps DB_INPUT users to create free forms easily. You can design your input form in an order different from the fields defined on the database. DB_INPUT will use the minimal number between non-empty cells in display and relation list. For example, if you have 5 non- empty cells in your display and 6 numbers in your relation list, then DB_INPUT takes the first 5 as your relation list. If you have 10 non-empty cells in your display and 5 numbers in you relation list, then DB_INPUT scans only the first 5 non-empty cells from the display. The rest cells will be displayed but have not effect in editing. DB_INPUT entry: DB_INPUT entry ______________ DB_INPUT accepts the following formats of data: - text string. - date number. The format of the date number is defined in date string Form. It is Defaults dialog (to get the Form Defaults Default dialog, you should select menu item Defaults Default Defaults under menu title Sheet). NOTE: the function DATE is Sheet NOTE hard-coded to read date in the sequence of month, day, and year. If you change the date string Date1, Date2 or Date1 Date2 Date3, they just change the appearance of your Date3 spreadsheet display. They will not change the input sequence of the DATE function. That is why some users from England saying that the new date format does not work. However, DB_INPUT reads date in the sequence date string Form defined. So, if you change the date string Form Form to dd-mm-yy, then Oct 26th, 1989 should be entered Form 26-10-89. 26-10-89 - numeric number. DB_INPUT will not accept formula at this moment, but you can enter formula in your query forms. NOTE1: NOTE1 the criteria uses the standard method to calculate result. If you want to find the persons who are hired before March 1st,83. You must enter the formula <date(3,1,83), but not <3-1-83. <date(3,1,83) <3-1-83 _______ NOTE2: NOTE2 You must provide a sample record for DB_INPUT to know D 11 SHEET -- Database Part By Chor-ming Lung November 1, 1989 what kind of entry the field is. Otherwise, all entries are treated as text string. So, you must enter at least one record in your database and reformat them to the desired format. Once you get it done, DB_INPUT have enough idea to treat each field fairly. BUTTON BUTTON Syntax: BUTTON name$, cell Description: Do you remember the 6 empty buttons in our DB_INPUT DB_INPUT dialog? They are user-defined buttons. Each user-defined button has a name and a subroutine that is called when the user-defined button is selected. Up to 6 buttons can be defined. Only the first 8 characters of the name$ will be displayed on the mouse buttons. Example: Instead of running from cell L11, we can run our program on L14. L14 has the BASIC statements: ! BUTTON "Rec #",K16: GOTO L11 ! BUTTON "Rec #",K16: GOTO L11 L16: ! MESSAGE "Record number:| "+STR$(CUR_REC) ! MESSAGE "Record number:| "+STR$(CUR_REC) L17: ! RETURN ! RETURN CUR_REC is the function which returns the current record CUR_REC number in our DB_INPUT. It is a must to end your subroutine with RETURN. D 12 SHEET -- Database Part By Chor-ming Lung November 1, 1989 Phone Book II: _____________ It is time to write a database program using our fancy DB_INPUT command. Do you remember the address book? We are going to create our address book database. Here is the field names of our database: Name Street City State Zip Phone number _________________________________________________ Column A holds field Name, B Street, C City, D State, E Zip and F Name Street City State Zip Phone number. I have reserved the first record to be the sender. If Phone number you want to print your name and the receiver's name on the envelop, you should change the record of mine to yours. For the time being, we just do nothing to our database. Let us look at our phone book program. It starts from cell IA1. IA1 :'Phone book II IA2 :\- IA3 :! BUTTON "Dial",IB1 IA4 :! BUTTON "Envelop",IB10 IA5 :! ERASE IE2..IJ10 IA6 :! DB_INPUT A1..F1000,IC2..ID10,IE1..IJ10,"1,2,3,4,5,6" IA7 :! END SHEET BASIC will skip text string and starts its execution from cell IA3. IA3, IA4 creates 2 user-defined buttons on our Input form. The first button is called "Dial" and the second one is "Envelop". If the "Dial" button is selected, subroutine starts from IB1 will be called. If "Envelop" button is selected, subroutine in IB10 will be called. IA5 It is not really necessary. It clears up previous criteria we make. IA6 Our database control command. Its database is in A1..F1000. We use F1000 because we do not want to change the ending cell from time to time. Our display area is in IC2..ID10. Our relation list shows that the display cells are stored in the same order as our fields. IE1..IJ10 is our query range. IA7 End of our program. Let us look at our dialing routine IB1 :'Dial phone Number IB2 :\- IB3 :! REDIRECT TO "aux:" IB4 :! PRINT "ATDT";F1[0, CUR_REC]$ IB5 :! REDIRECT TO "con:" IB6 :! RETURN IB3 Change the direction of all subsequent PRINT commands to "AUX:" -- serial port. IB4 ATDT means (ATtension Dial Tone). F1 is our Field which holds the phone number. CUR_REC is a function which works D 13 SHEET -- Database Part By Chor-ming Lung November 1, 1989 with DB_INPUT only. It returns the position of the current record. If CUR_REC is 3, F1[0,3] means cell F4. Do not forget to add $ to print the string on cell F4. If no $, it $ $ will print the value on the cell. It is 0 for text string. IB5 Change the direction of PRINT commands back to console. Let us look at our envelop printing routine IB10 :'Print address on envelop IB11 :\- IB12 :! REDIRECT TO "prn:" IB13 :!x=2:t=1: GOSUB IB20 IB14 :!x=32:t= CUR_REC: GOSUB IB20 IB15 :! PRINT CHR$(12) IB16 :! REDIRECT TO "con:" IB17 :! RETURN IB12 Change the direction of PRINT to "prn:" -- printer. IB13 x and t are variables for subroutine IB20. x changes the print-head position to x and t holds the record number we want to print. Parameter passing is not implemented yet. So, we use global variables to pass parameters. We print the first record (the sender address). IB14 Print the current record. IB15 Form feed. IB16 Change the direction of PRINT comamnds back to console. IB20 :'Print Address IB21 :\- IB22 :! ERASE IM2..IM8 IB23 :!IM2$=A1[0,t]$ IB24 :!i=1:p=1:temp$=B1[0,t]$ IB25 :! LOOP IB26 :!c= POS(";",temp$,p) IB27 :! EXIT IF c=0 IB28 :!IM2[0,i]$= MID$(temp$,p,c-p):p=c+1:i=i+1 IB29 :! ENDLOOP IB30 :!IM2[0,i]$= MID$(temp$,p,100):i=i+1 IB31 :!IM2[0,i]$=C1[0,t]$+", "+D1[0,t]$+" "+E1[0,t]$ IB32 :! PRINT_BLOCK IM1..IM8,x,x+40 IB33 :! RETURN IB22 Erase cells from IM2 to IM8. This block is used for temporarily storage. IB23 IM2 holds the name of the record being printed. temp$ holds the Street. NOTE: The street part may contain more than one line. It is true for country other than U.S.A. IB24..IB30The Street field recognizes semicolon as a line separator. The routine in IB24..IB28 separates the lines and stores its result in IM3 down. IB31 This statement combines City, State Zip in one line. City, State Zip IB32 Print cells from IM1..IM8. The print-head starts from position x up to 40 characters. D 14